﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
       // public static WS_Service.Service1 s1 = new ConsoleApplication1.WS_Service.Service1();
        public static bool keepRunning = true;

        static void Main(string[] args)
        {
            ShowMenu();
        }

        static void ShowMenu()
        {
            while(keepRunning)
            {
                Console.WriteLine(@"");
                Console.WriteLine(@"*** WindStream WebService Tester ***");
                Console.WriteLine(@"");
                Console.WriteLine(@"1 - Display Installation_Day_Table");
                Console.WriteLine(@"2 - Display Bus_Power_Minute_Table");

                Console.WriteLine(@"");
                Console.Write(@"Enter Your Selection: ");
                string selection = Console.ReadLine();
                Console.WriteLine(@"");

                if (selection.Equals(@"1"))
                    DisplayAzureData();
                else if (selection.Equals(@"2"))
                    DisplayBusMinutes();
                else
                    Console.WriteLine(@"Incorrect Selection");
            }
        }

        // STEP TWO - output 30 days worth of data from the day table
        //          - output all days from the minute table - what is the most recent data?
        //          - output 1 days worth of minutes from the minute table

        static void DisplayAzureData()
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM installation_day_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", "65edcc55-e7b6-4d53-96ac-b3e6f071ded2");

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                for (int x = 0; x != installDays.Rows.Count; x++)
                {
                    //Console.Out.WriteLine(installDays.Rows[x]["UtcMinute"].ToString() + " " + installDays.Rows[x]["Joules"].ToString());
                    Console.Out.WriteLine(installDays.Rows[x]["UtcMinute"].ToString() + " " + installDays.Rows[x]["Joules"].ToString());
                }

                cn.Close();
            }
        }

        /*
         * UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
         */ 

        // WHAT IS THE HELSINKI ID?




        // STEP THREE 

        static void DisplayBusMinutes()
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                DateTime startDay = DateTime.Today.AddDays(-1);

                DateTime endDay = startDay.AddDays(1);

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID AND UtcMinute BETWEEN @start and @end", cn);
                cmd.Parameters.AddWithValue("@InstallationID", "6c2c0c0e-ce4b-45c4-8762-97fdd3f09f97");
                cmd.Parameters.AddWithValue("@start", startDay);
                cmd.Parameters.AddWithValue("@end", endDay);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable data = new DataTable();
                da.Fill(data);

                cn.Close();

                DataTable dt = new DataTable("MyTable");

                DataColumn dc0 = new DataColumn("Hour");
                dt.Columns.Add(dc0);

                DataColumn dc1 = new DataColumn("Energy");
                dt.Columns.Add(dc1);

                DateTime startOfWeek = DateTime.Now;

                for (int x = 0; x < 24; x++)
                {
                    DateTime thisHour = startDay.AddHours(x);
                    DataRow dr = dt.NewRow();
                    dr[0] = thisHour.ToString("hh tt");
                    dr[1] = 0;

                    float totalJoules = 0;

                    for (int y = 0; y != data.Rows.Count; y++)
                    {
                        DateTime rowDate = DateTime.Parse(data.Rows[y]["UtcMinute"].ToString());
                        Console.Out.WriteLine(data.Rows[y]["UtcMinute"].ToString() + " JOULES = " + data.Rows[y]["Joules"].ToString());
                        if (rowDate.Hour == thisHour.Hour)
                        {
                            totalJoules += (float)data.Rows[y]["Joules"] / 3600;
                        }
                    }

                    dr[1] = totalJoules;

                    dt.Rows.Add(dr);
                }
                //return dt;
            }
        }

        // STEP THREE   - sum up all the minutes for each day in the buspower_minute_table
        //              - put them into the installation_day_table (with the right installationID)
        // OLD INSTALL ID = 65edcc55-e7b6-4d53-96ac-b3e6f071ded2
        // NEW INSTALL ID = 6c2c0c0e-ce4b-45c4-8762-97fdd3f09f97
        static void UpdateMinuteTable()
        {
            using (SqlConnection cn = new SqlConnection("Server=tcp:mclv8ptxcr.database.windows.net;Database=tmDB1;User ID=wsAdmin3000;Password=W!nD5tR34mDB;Trusted_Connection=False;Encrypt=True;"))
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM buspower_minute_table WHERE InstallationID = @InstallationID", cn);
                cmd.Parameters.AddWithValue(@"InstallationID", "6c2c0c0e-ce4b-45c4-8762-97fdd3f09f97");

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable installDays = new DataTable();
                da.Fill(installDays);

                for (int x = 0; x != installDays.Rows.Count; x++)
                {
                    //Console.Out.WriteLine(installDays.Rows[x]["UtcMinute"].ToString());
                    //Console.Out.WriteLine(installDays.Rows[x]["InstallationID"].ToString());
                    string thisDate = installDays.Rows[x]["UtcMinute"].ToString().Substring(0, 10);
                    Console.Out.WriteLine(thisDate);
                }

                cn.Close();
            }
        }


/*
        static void DeleteLog()
        {
            Console.Write(@"Enter Install ID of log file to delete: ");
            string installID = Console.ReadLine();
            string file_status = s1.Delete_Log(installID);
            Console.WriteLine(@"");
            Console.WriteLine(file_status);
        }

        static void PrintLog()
        {
            Console.Write(@"Enter Install ID of log file to print: ");
            string installID = Console.ReadLine();
            Console.WriteLine(@"*** START OF FILE ***");
            string log_contents = s1.Print_Log(installID);
            Console.WriteLine(log_contents);
            Console.WriteLine(@"*** END OF FILE ***");
        }

        static void PrintSQLLog()
        {
            Console.WriteLine(@"*** START OF LOG ***");
            string log_contents = s1.SQL_Print_Log();
            Console.WriteLine(log_contents);
            Console.WriteLine(@"*** END OF LOG ***");
        }

        static void CreateDB()
        {
            string status = s1.SQL_Create_Packet_Log();
            Console.WriteLine(status);
        }

        static void LogPacket()
        {
            string time = DateTime.Now.ToString();
            Console.Write(@"Enter Install ID to Log: ");
            string installID = Console.ReadLine();
            Console.Write(@"Enter TurboMill ID to Log: ");
            string tmID = Console.ReadLine();
            Console.Write(@"Enter Data to Log: ");
            string data = Console.ReadLine();
            string success = s1.SQL_Log_Packet(time, installID, tmID, data);
            Console.WriteLine(@"");
            Console.WriteLine(success);
        }

        static void GenerateRandomPackets()
        {
            DateTime currentTime = DateTime.Now;
            string installID = @"2";
            Random random = new Random();

            for(int x=0; x!= 24; x++)
            {
                DateTime thisTime = currentTime.AddDays(-x);

                for (int tmID = 1; tmID < 4; tmID++)
                {
                    int randomWatts = random.Next(0, 400);
                    string success = s1.SQL_Log_Packet(thisTime.ToString(), installID, tmID.ToString(), randomWatts.ToString());
                }
            }

            Console.WriteLine(@"Random Month of Weekly Data Generated");
        }

        static void DeleteSQLLog()
        {
            string status = s1.SQL_Delete_Log();
            Console.WriteLine(status);
        }
 * */
    }
}
